﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.IO;
using System.Xml;
using System.Windows.Markup;
using System.Text.RegularExpressions;

namespace OneSchema
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            btnClearAttributes_Click(null, null);

#if DEBUG
            tbRelation.Text = "R";
            tbAttributeInput.Text = "a-h";
            btnRelationAdd_Click(null, null);

            autoFDLeft.Text = "a,b";
            autoFDRight.Text = "f";
            btnFDAdd_Click(null, null);

            autoFDLeft.Text = "a,c";
            autoFDRight.Text = "g";
            btnFDAdd_Click(null, null);

            autoFDLeft.Text = "a,d";
            autoFDRight.Text = "b";
            btnFDAdd_Click(null, null);

            autoFDLeft.Text = "a,e";
            autoFDRight.Text = "h";
            btnFDAdd_Click(null, null);


            autoFDLeft.Text = "b";
            autoFDRight.Text = "c,e";
            btnFDAdd_Click(null, null);


            autoFDLeft.Text = "e";
            autoFDRight.Text = "b";
            btnFDAdd_Click(null, null);

            autoFDLeft.Text = "c";
            autoFDRight.Text = "d";
            btnFDAdd_Click(null, null);



#endif
        }

        private void btnRelationAdd_Click(object sender, RoutedEventArgs e)
        {
            string relationName = tbRelation.Text.Trim();
            if (relationName.Length == 0) return;

            string inputAttribute = tbAttributeInput.Text.Trim();
            if (inputAttribute.Length == 0) return;

            // Check for duplicate relation with same name
            foreach (Relation r in lbRelationsList.Items)
            {
                if (r.Name == relationName)
                {
                    MessageBox.Show("A relation with name " + relationName + " already exists.");
                    return;
                }
            }

            AttributeSet attrSet = parseAttributes(inputAttribute);

            Relation newR = new Relation(attrSet);
            newR.Name = relationName;

            lbRelationsList.Items.Add(newR);
            

            //Add new suggestion entry to the auto-complete box (for FD).
            List<string> attributeSuggestion = new List<string>();
            foreach (string attribute in AttributeSet.UniversalSet)
            {
                attributeSuggestion.Add(attribute);
            }
            autoFDLeft.ItemsSource = attributeSuggestion;
            autoFDRight.ItemsSource = attributeSuggestion;

            tbAttributeInput.Text = "";
            tbRelation.Text = "";
            tbRelation.Focus();
        }

        /// <summary>
        /// Parse string of input attributes
        /// e.g. "a,b,c" or "a-z"
        /// </summary>
        private AttributeSet parseAttributes(string inputAttribute)
        {
            if (Regex.IsMatch(inputAttribute, @"\A[a-z]-[a-z]\Z") ||
                Regex.IsMatch(inputAttribute, @"\A[A-Z]-[A-Z]\Z"))
            {
                AttributeSet attrSet = new AttributeSet();

                for (int i = inputAttribute[0]; i <= inputAttribute[2]; i++)
                {
                    string attr = "" + (char)i;
                    AttributeSet.UniversalSet.Add(attr);
                    attrSet.Add(attr);
                }
                return attrSet;
            }
            else
            {
                foreach(string attr in inputAttribute.Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries))
                    AttributeSet.UniversalSet.Add(attr.Trim());
                return new AttributeSet(inputAttribute);
            }
        }

        private void btnClearAttributes_Click(object sender, RoutedEventArgs e)
        {
            lbFDsList.Items.Clear();
            lbRelationsList.Items.Clear();
            AttributeSet.UniversalSet = new UniversalBitSet<string>();
        }

        private void btnFDAdd_Click(object sender, RoutedEventArgs e)
        {
            if(autoFDLeft.Text.Trim() == "")
                return;

            FD newFD = null;
            try
            {
                newFD = new FD(autoFDLeft.Text, autoFDRight.Text);

                if (!lbFDsList.Items.Contains(newFD))
                    lbFDsList.Items.Add(newFD);
                else
                    MessageBox.Show("The functional dependency " + (new FD(autoFDLeft.Text, autoFDRight.Text)).ToString() + " is already added.", "FD Already Added", MessageBoxButton.OK, MessageBoxImage.Warning);
            }
            catch (Exception)
            {
                MessageBox.Show("Invalid functional dependency.", "Invalid FD", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally 
            {
                autoFDLeft.Text = "";
                autoFDRight.Text = "";
                autoFDLeft.Focus();
            }

            
        }


        private void autoFDRight_TextChanged(object sender, RoutedEventArgs e)
        {
            string currentInput = autoFDRight.Text;
            string[] enteredAttributesInFD = currentInput.Split(',');

            if (currentInput.EndsWith(","))
            {
                List<string> attributeSuggestion = new List<string>();
                foreach (string attribute in AttributeSet.UniversalSet)
                {
                    if (!enteredAttributesInFD.Contains(attribute))
                        attributeSuggestion.Add(currentInput + attribute);
                }

                autoFDRight.ItemsSource = attributeSuggestion;
            }
            else if (currentInput.Equals(""))
            {
                List<string> attributeSuggestion = new List<string>();
                foreach (string attribute in AttributeSet.UniversalSet)
                {
                    attributeSuggestion.Add(currentInput + attribute);
                }

                autoFDRight.ItemsSource = attributeSuggestion;
            }
        }

        private void autoFDLeft_TextChanged(object sender, RoutedEventArgs e)
        {
            string currentInput = autoFDLeft.Text;
            string[] enteredAttributesInFD = currentInput.Split(',');

            if (currentInput.EndsWith(","))
            {
                List<string> attributeSuggestion = new List<string>();
                foreach (string attribute in AttributeSet.UniversalSet)
                {
                    if (!enteredAttributesInFD.Contains(attribute))
                        attributeSuggestion.Add(currentInput + attribute);
                }

                autoFDLeft.ItemsSource = attributeSuggestion;
            }
            else if (currentInput.Equals(""))
            {
                List<string> attributeSuggestion = new List<string>();
                foreach (string attribute in AttributeSet.UniversalSet)
                {
                    attributeSuggestion.Add(currentInput + attribute);
                }

                autoFDLeft.ItemsSource = attributeSuggestion;
            }
        }

        private void btnClearFDs_Click(object sender, RoutedEventArgs e)
        {
            lbFDsList.Items.Clear();
        }

        private void btnProceed_Click(object sender, RoutedEventArgs e)
        {
            // Get all input FDs
            FDSet fdSet = new FDSet();
            foreach (FD fd in lbFDsList.Items)
                fdSet.Add(fd);

            // Associate all input relation with fds and compute keys
            foreach (Relation r in lbRelationsList.Items)
            {
                r.FunctionalDependencies = fdSet;
                r.ComputeKeys();
            }

            DecomposeInputRelations(false);

            gridFirstPage.Visibility = Visibility.Hidden;
            gridSecondPage.Visibility = Visibility.Visible;
        }

        private void btnBackFirstPage_Click(object sender, RoutedEventArgs e)
        {
            gridFirstPage.Visibility = Visibility.Visible;
            gridSecondPage.Visibility = Visibility.Hidden;

            //Reset the second page.
            cb_decompositionMethod.SelectedIndex = 0;
        }

        private void lbFDsList_MouseDoubleClick(object sender, MouseButtonEventArgs e)
        {
            if (lbFDsList.SelectedIndex > -1)
            {
                lbFDsList.Items.Remove(lbFDsList.SelectedItem);
                autoFDLeft.Focus();
            }
        }

        private RelationNode GenerateRelations(bool BCNF, Relation r, FD violatedFD)
        {
            RelationNode parentNode = new RelationNode(r);

            Queue<RelationNode> q = new Queue<RelationNode>();

            q.Enqueue(parentNode);

            while (q.Count() != 0)
            {
                RelationNode n = q.Dequeue();

                Relation relationToBeDecomposed = n.NodeItem;

                relationToBeDecomposed.FindViolations(BCNF);

                if (relationToBeDecomposed.ViolatedFDs.Count() != 0)
                {
                    if (violatedFD == null)
                        violatedFD = relationToBeDecomposed.ViolatedFDs.First();

                    Relation r1, r2;
                    relationToBeDecomposed.Decompose(violatedFD, out r1, out r2);
                    r1.Name = relationToBeDecomposed.Name + "1";
                    r2.Name = relationToBeDecomposed.Name + "2";

                    RelationNode left = new RelationNode(r1);
                    RelationNode right = new RelationNode(r2);


                    n.Children.Add(left);
                    n.Children.Add(right);
                    q.Enqueue(left);
                    q.Enqueue(right);

                    violatedFD = null;
                }
            }

            return parentNode;
        }

        private void tv_decompositionTree_SelectedItemChanged(object sender, RoutedPropertyChangedEventArgs<object> e)
        {
            // Force refresh if necessary
            stkRelationDetails.DataContext = null;

            // Selected item might be 'disconnected'
            if (tv_decompositionTree.SelectedItem != null && tv_decompositionTree.SelectedItem.GetType() == typeof(RelationNode))
            {
                Relation selectedRelation = ((RelationNode)tv_decompositionTree.SelectedItem).NodeItem;
                stkRelationDetails.DataContext = selectedRelation;

                //Doing 2NF violation checking here.
                bool is2NFViolated = selectedRelation.Find2NFViolations();
                if (is2NFViolated)
                {
                    tb_Remarks.Text = "Remarks:";
                    lblRemarks.Content = "This relation violated 2NF as well.";
                }
                else
                {
                    tb_Remarks.Text = "";
                    lblRemarks.Content = "";
                }
                
            }
            else
                stkRelationDetails.DataContext = null;
        }

        private void DecomposeInputRelations(bool BCNF)
        {
            try
            {
                List<RelationNode> decompTree = new List<RelationNode>();

                foreach (Relation r in lbRelationsList.Items)
                    decompTree.Add(GenerateRelations(BCNF, r, null));

                tv_decompositionTree.ItemsSource = decompTree;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            UpdateFinalRelations();
        }

        private void cb_decompositionMethod_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            DecomposeInputRelations(cb_decompositionMethod.SelectedIndex == 1);
            tv_decompositionTree_SelectedItemChanged(null, null);
        }

        private void gridMainPageLogo_MouseDown(object sender, MouseButtonEventArgs e)
        {
            gridMainPageLogo.Visibility = Visibility.Hidden;
        }

        private void lb_finalDecompositionResult_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if(lb_finalDecompositionResult.SelectedIndex >= 0)
                stkRelationDetails.DataContext = lb_finalDecompositionResult.SelectedItem;
        }

        private void UpdateFinalRelations()
        {
            lb_finalDecompositionResult.Items.Clear();
            foreach (RelationNode node in tv_decompositionTree.ItemsSource)
                AddLeafRelationNode(node);
        }

        private void AddLeafRelationNode(RelationNode node)
        {
            if (node.Children.Count == 0)
                lb_finalDecompositionResult.Items.Add(node.NodeItem);
            else
            {
                foreach (RelationNode child in node.Children)
                    AddLeafRelationNode(child);
            }
        }

        private void tbAttributeInput_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.Enter)
                btnRelationAdd_Click(null, null);
        }

        private void autoFDRight_PreviewKeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.Enter)
                btnFDAdd_Click(null, null);
        }

        private void ListBox_MouseDoubleClick(object sender, MouseButtonEventArgs e)
        {
            ListBox lb = (ListBox)sender;
            FD violatedFd = (FD)lb.SelectedItem;

            RelationNode node = (RelationNode)tv_decompositionTree.SelectedItem;
            node.Children.Clear();

            // check if violatedFd is null
            Relation r1, r2;
            Relation relationToBeDecomposed = node.NodeItem;
            relationToBeDecomposed.Decompose(violatedFd, out r1, out r2);
            r1.Name = relationToBeDecomposed.Name + "1";
            r2.Name = relationToBeDecomposed.Name + "2";

            bool BCNF = cb_decompositionMethod.SelectedIndex==1;
            node.Children.Add(GenerateRelations(BCNF, r1, null));
            node.Children.Add(GenerateRelations(BCNF, r2, null));

            UpdateFinalRelations();
        }

        private void lbAttributeTypes_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (lbAttributeTypes.SelectedIndex > -1)
            {
                //Retrieve the type (if any).
                object attriuteTypeTag = ((ListBoxItem)lbAttributeTypes.Items[lbAttributeTypes.SelectedIndex]).Tag;
                string type = "";
                if (attriuteTypeTag != null)
                    type = attriuteTypeTag.ToString();

                //Put the type (if any) to the textbox.
                tbDatatype.Text = type;

                //Focus on the textbox.
                tbDatatype.Focus();
            }
        }

        private void btnAssignType_Click(object sender, RoutedEventArgs e)
        {
            if (lbAttributeTypes.SelectedIndex > -1)
            {
                ListBoxItem selectedRelation = (ListBoxItem)lbAttributeTypes.Items[lbAttributeTypes.SelectedIndex];
                string itemString = selectedRelation.Content.ToString();

                if (selectedRelation.Tag != null)
                    selectedRelation.Content = itemString.Substring(0, itemString.Length -
                        selectedRelation.Tag.ToString().Length - 9) +
                        " (Type: " + tbDatatype.Text + ")";
                else
                    selectedRelation.Content += " (Type: " + tbDatatype.Text + ")";

                selectedRelation.Tag = tbDatatype.Text;

                lbAttributeTypes.Focus();
            }
        }

        private void btnBackSecondPage_Click(object sender, RoutedEventArgs e)
        {
            lbAttributeTypes.SelectedIndex = -1;
            lbAttributeTypes.Items.Clear();
            tbDatatype.Text = "";

            gridThirdPage.Visibility = Visibility.Hidden;
        }

        private void btnExportSchema_Click(object sender, RoutedEventArgs e)
        {
            gridThirdPage.Visibility = Visibility.Visible;

            foreach (string attribute in AttributeSet.UniversalSet)
            {
                ListBoxItem att = new ListBoxItem();
                att.Content = attribute;
                lbAttributeTypes.Items.Add(att);
            }

            gridFourthPage.Visibility = Visibility.Hidden;
        }

        static string GenerateSQLSchema(Relation r, List<string> attributeTypes, bool oracle)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("CREATE TABLE " + r.Name + "(\r\n");

            AttributeSet attrSet = r.Attributes;

            int i = 0;
            foreach (string attr in attrSet)
            {
                sb.Append("\t" + attr + " " + attributeTypes[i] + ",\r\n");
                i++;
            }

            if (r.Keys.Count() != 0)
            {
                AttributeSet keySet = r.Keys.First();

                if (oracle) sb.Append("\tCONSTRAINT key ");
                if (!oracle) sb.Append("\t");

                sb.Append("PRIMARY KEY(");
                foreach (string attr in keySet)
                {
                    sb.Append(attr + ",");
                }

                sb.Remove(sb.Length - 1, 1);
                sb.Append(")");
            }

            if (r.Keys.Count() == 0) sb.Remove(sb.Length - 2, 1);
            sb.Append(")\r\n\r\n");

            return sb.ToString();
        }

        private void btnSchemaGeneration_Click(object sender, RoutedEventArgs e)
        {
            //Check if all attributes have type.
            bool attributeTypesOkay = true;
            foreach (ListBoxItem item in lbAttributeTypes.Items)
            {
                if (item.Tag == null || item.Tag.ToString().Trim().Equals(""))
                {
                    attributeTypesOkay = false;
                    break;
                }
            }

            if (attributeTypesOkay)
            {
                //By default, it is MySQL.
                rbOracle.IsChecked = true;
                rbMySql.IsChecked = true;
            }
            else 
            {
                MessageBox.Show("There is attribute that has no type.", "Cannot Generate Schema", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void generateSchemaBasedOnDifferentScheme(bool oracle) 
        {
            if (tbSchemaGenerated != null)
                tbSchemaGenerated.Text = "";

            foreach (Relation r in lb_finalDecompositionResult.Items)
            {
                //Retrieve all the attributes in the relation r.
                String[] attributesInR = r.Attributes.ToArray();

                //Construct a list of corresponding attribute types.
                List<string> attributeTypes = new List<string>();
                for (int i = 0; i < attributesInR.Length; i++)
                {
                    //Find the type of the attribute.
                    foreach (ListBoxItem item in lbAttributeTypes.Items)
                    {
                        if (item.Content.ToString().Substring(0, item.Content.ToString().Length - item.Tag.ToString().Length - 9)
                            .Equals(attributesInR[i]))
                            attributeTypes.Add(item.Tag.ToString());
                    }
                }

                tbSchemaGenerated.Text += GenerateSQLSchema(r, attributeTypes, oracle);
            }

            gridFourthPage.Visibility = Visibility.Visible;
        }

        private void btnBackThirdPage_Click(object sender, RoutedEventArgs e)
        {
            gridFourthPage.Visibility = Visibility.Hidden;
        }

        private void rbMySql_Checked(object sender, RoutedEventArgs e)
        {
            //It is MySQL Schema!
            generateSchemaBasedOnDifferentScheme(false);
        }

        private void rbOracle_Checked(object sender, RoutedEventArgs e)
        {
            //It is the Oracle Schema!
            generateSchemaBasedOnDifferentScheme(true);
        }

        private void tbDatatype_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.Enter)
                btnAssignType_Click(null, null);
        }
    }
}
